package br.unirio.ticketnator.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import br.unirio.ticketnator.model.Ticket;

public class TicketDAO implements ITicketDAO {
	
	private Ticket carrega (ResultSet rs) throws SQLException
	{
		Ticket ticket = new Ticket ();
		ticket.setIdTicket(rs.getInt("id"));
		ticket.setTitulo(rs.getString("titulo"));
		ticket.setNomeComponenteCorrespondente(rs.getString("nonmecomponentecorrespondente" ));
		ticket.setDescricao(rs.getString("descricao"));
		ticket.setStatus(rs.getInt("status"));
		ticket.setNomeOperadorResponsavel(rs.getString("nomeoperadorresponsavel"));
		ticket.setIdSistemaCorrespondente(rs.getInt("idSistemaCorrespondente"));
		
		return ticket;
	}
	
	@Override
	public Ticket getTicket(int id) 
	{
		Connection c = SupportDAO.getConnection();
		
		if( c == null)
			return null;
		
		Ticket ticket = null;
		
		try
		{
			PreparedStatement ps = c.prepareStatement("SELECT * FROM TB_TICKET WHERED ID_TICKET = id");
			ps.setLong(1, id);
			
			ResultSet rs = ps.executeQuery();
			
			if(rs.next())
				ticket = carrega(rs);
			c.close();
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
		}
		
		return ticket;
		
		
	}
	
	@Override
	public int conta() {
		Connection c = SupportDAO.getConnection();
		
		if(c == null)
			return 0;
		
		int numero = 0;
		
		try
		{
			PreparedStatement ps = c.prepareStatement("SELECT COUNT(*) FROM TB_TICKET");
			ResultSet rs = ps.executeQuery();
			
			if (rs.next())
				numero = rs.getInt(1);
			
			c.close();
			
		} catch (SQLException e){
			SupportDAO.log(e.getMessage());
		}
		
		return numero;
	}
	
	@Override
	public List<Ticket> lista(int pagina, int tamanho) {
		
		Connection c = SupportDAO.getConnection();
		
		if(c == null)
			return null;
		
		List<Ticket> lista = new ArrayList<Ticket>();
		
		try
		{
			PreparedStatement ps = c.prepareStatement("SELECT * FROM TB_TICKET ORDER BY TITULO LIMIT ? OFFSET ?");
			ps.setInt(1, tamanho);
			ps.setInt(2, pagina * tamanho);
			ResultSet rs = ps.executeQuery();
			
			while (rs.next())
				lista.add(carrega(rs));
			
			c.close();
			
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
		}
		
		return lista;
	}
	
	@Override
	public boolean insereTicketDAO(Ticket ticket) 
	{
		Connection c = SupportDAO.getConnection();
		
		String insertTicket = "INSERT INTO `db_ticketnator`.`tb_ticket` " +
		"(`TITULO`, `COMPONENTE`, `DESCRICAO`, `STATUS_TICKET`, `OPERADOR`, `SISTEMA_CORRESPONDENTE`)" +
		" VALUES ('?', '?', '?', '?', '?', '?');";
		
		if (c == null)
			return false;
		try
		{
			
			
			/*CallableStatement cs = c.prepareCall("{call InsereTicket(?, ?, ?, ?, ?, ?, ?)}"); 
			cs.setString(1, ticket.getTitulo());
			cs.setString(2, ticket.getNomeComponenteCorrespondente());
			cs.setString(3, ticket.getDescricao());
			cs.setInt(4, ticket.getStatus());
			cs.setString(5, ticket.getNomeOperadorResponsavel());
			cs.setInt(6, ticket.getidSistemaCorrespondente());
			cs.registerOutParameter(7, Types.INTEGER);
			
			int id = cs.getInt(7);
			ticket.setIdTicket(id);
			cs.execute();*/
					
			PreparedStatement ps = c.prepareStatement(insertTicket);
			ps.setString(1, ticket.getTitulo());
			ps.setString(2, ticket.getNomeComponenteCorrespondente() );
			ps.setString(3, ticket.getDescricao());
			ps.setInt(4, ticket.getStatus());
			ps.setString(5, ticket.getNomeOperadorResponsavel());
			ps.setInt(6, ticket.getIdSistemaCorrespondente());
			
			ps.execute();
					
					
			
			c.close();
			return true;
			
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
			return false;
		}
	}
	
	@Override
	public boolean atualizaTicketDAO(Ticket ticket)
	{
		Connection c = SupportDAO.getConnection();
		String updateTicket = "UPDATE TB_TICKET"
							+"SET TITULO = ?,"
							+"COMPONENTE = ?,"
							+"DESCRICAO = ?,"
							+"STATUS_TICKET = ?,"
							+"OPERADOR = ?,"
							+"SISTEMA_CORRESPONDENTE = (SELECT ID_SISTEMA FROM TB_SISTEMA WHERE NOME_SISTEMA = ?)"
							+"WHERE ID_TICKET = ?;";
		
		if ( c == null)
			return false;
		
		try
		{
			/*CallableStatement cs = c.prepareCall("{call AtualizaTicket(?, ?, ?, ?, ?, ?, ?)}");
			cs.setInt(1, ticket.getIdTicket());
			cs.setString(2, ticket.getTitulo());
			cs.setString(4, ticket.getNomeComponenteCorrespondente());
			cs.setString(4, ticket.getDescricao());
			cs.setInt(5, ticket.getStatus());
			cs.setString(6, ticket.getNomeOperadorResponsavel());
			cs.setInt(7, ticket.getidSistemaCorrespondente());
			cs.execute();*/
			
			PreparedStatement ps = c.prepareStatement(updateTicket);
			
			ps.setString(1,ticket.getTitulo());
			ps.setString(2, ticket.getNomeComponenteCorrespondente());
			ps.setString(3, ticket.getDescricao());
			ps.setInt(4,ticket.getStatus());
			ps.setString(5, ticket.getNomeOperadorResponsavel());
			ps.setInt(6, ticket.getIdTicket());
			
			
			ps.execute();
			
			
			
			c.close();
			
			return true;
			
		} catch (SQLException e){
			SupportDAO.log(e.getMessage());
			return false;
		}
	}
	
	@Override
	public boolean removeTicketDAO(int id) 
	{
		Connection c = SupportDAO.getConnection();
		String removeTicket = 	"DELETE"
								+"FROM TB_TICKET"
								+"WHERE ID_TICKET = ?;";	
		
		if (c == null)
			return false;
		
		try
		{
			/*CallableStatement cs = c.prepareCall("{call RemoveTicket(?)}");
			cs.setInt(1, id);
			cs.execute();*/
			
			PreparedStatement ps = c.prepareStatement(removeTicket);
			
			ps.setInt(1, id);
		
			
			
			c.close();
			return true;
			
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
			return false;
		}
	}
        
        
        @Override
        public int getIdSistemaByNome(String nome){
            Connection c = SupportDAO.getConnection();
            String sql = "SELECT ID_SISTEMA FROM TB_SISTEMA WHERE NOME_SISTEMA = ?";
            int numero = 0;
            if (c == null)
			return 0;
		
		try
		{
			PreparedStatement ps = c.prepareStatement(sql);
                        
                        ps.setString(1,nome);
			
			ResultSet rs = ps.executeQuery();
			
			if (rs.next())
				numero = rs.getInt(1);
			c.close();
			return numero;
			
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
			return 0;
		}

            
            
            
            
        }
	
}
